<?php

class OrderImport extends ImportBase
{
	private $main_table = 'orders';
	private $item_table = 'orders_products';
	
	private $api;
	
	protected $db;
	
	public function __construct()
	{
		global $config;
		//echo "{$config['db_host']},{$config['db_user']},{$config['db_pass']},{$config['db_name']}";
		$this->set_s_db( $config['db_host'],$config['db_user'],$config['db_pass'],$config['db_name'] );
		$this->api = Mage::getModel('catalog/category_api');;
		$this->db = Mage::getSingleton ( 'core/resource' )->getConnection ( 'core_write' );
		
		parent::__construct();
	}
	
		
	function run()
	{
		global $config;
		$category_index_array = array();
		$orders = $this->find_all_order();
				
		if( count( $orders ) ){
			echo 'try to import total of orders :'.count( $orders ).'<br />';
			foreach( $orders as $_order ){
				
				$order_id = $this->import_order( $_order['order_id'] );
				echo '$order_id='.$order_id.'<br />';
				//exit();
				$this->log( array( 'org_order_id' => $_order['order_id'],'order_id' => $order_id ) );
				$this->transation_commit();
			}
		}
	}
	
	
	
	public function find_all_order()
	{		
		
		$query = "select orders_id as order_id from {$this->main_table} p left join {$this->log_table} log ON p.orders_id = log.org_order_id where log.org_order_id is null order by date_purchased limit 1000";
		
		$this->s_db->query( $query );
		return $this->s_db->af();	
	}
	
	function import_order( $order_id )
	{
		
		$order_source = $this->readOrder( $order_id );
		
		//print_r( $order );
		$order = Mage::getModel("sales/order");
		$order->setData( $this->formatOrderData( $order_source[0] ) );
		$order->setBillingAddress( $this->formatOrderBillingAddress($order_source[0]),'billing' );
		$order->setShippingAddress( $this->formatOrderShippingAddress($order_source[0]),'shipping' );
		$order->setPayment( $this->formatOrderPayment( $order_source[0]['payment_method'] ) );
		
		//order item
		foreach( $order_source as $_order_item ){
			$order->addItem( $this->formatOrderItem( $_order_item ) );
		}
		
		//insert comment
		$order->setState(
							   null, 
							  $order->getStatus(),
							   $this->getOrderComment( $order_id ),
							   $notified = false
						   );
		$created_date = $order->getData('created_at');
		$updated_at = $order->getData('updated_at');
//exit();
		
		$order->save();
		$this->forceUpdateOrderDate( $order->getId(),$created_date,$updated_at );
		$shipping_data = $this->getShippmentData( $order_id );
		
		if( count( $shipping_data ) ){
			foreach( $shipping_data as $_shippment ){
				
				$this->importShippment( $order,$_shippment['carrier_name'],$_shippment['comments'] );
			}
		}
		//import shippment
		return $order->getId();
	}
	
	private function forceUpdateOrderDate( $order_id,$created_date,$updated_date )
	{
		//echo "update sales_flat_order set created_at='{$created_date}',updated_at='{$updated_date}' where entity_id 	='{$order_id}'";
		$this->db->query( "update sales_flat_order set created_at='{$created_date}',updated_at='{$updated_date}' where entity_id 	='{$order_id}'" );
		$this->db->query( "update sales_flat_order_grid set created_at='{$created_date}',updated_at='{$updated_date}' where entity_id 	='{$order_id}'" );
		return;
	}
	
	private function readOrder( $order_id )
	{
		$query = "select m.orders_id as order_id,
					m.orders_id as increase_id,
					m.customers_id as customer_id,
					m.order_total as grand_total,
					(
						case m.orders_status
						when 1 then 0
						when 2 then m.order_total
						when 3 then m.order_total
						when 4 then m.order_total
						when 100 then 0
						when 101 then 0
						when 111 then 0
						when 112 then 0
						when 113 then 0
						end
					) as total_paid,	
					m.orders_status as status,
					m.customers_name as name,
					m.customers_company as company,
					m.customers_street_address as street,
					m.customers_city as city,
					m.customers_postcode as postcode,
					m.customers_state as state,
					m.customers_country as country,
					m.customers_telephone as telephone,
					m.customers_email_address as email,
					m.delivery_name,
					m.delivery_company,
					m.delivery_street_address,
					m.delivery_suburb,
					m.delivery_city,
					m.delivery_postcode,
					m.delivery_state,
					m.delivery_country,
					m.billing_name,
					m.billing_company,
					m.billing_street_address,
					m.billing_suburb,
					m.billing_city,
					m.billing_postcode,
					m.billing_state,
					m.billing_country,
					m.cc_type,
					m.cc_owner,
					m.cc_number,
					m.cc_expires,
					m.cc_cvv,
					m.date_purchased as created_at,
					m.orders_date_finished as updated_at,
					m.currency as currency_code,
					m.order_tax as tax_amount,
					
					m.payment_module_code as payment_method,
					m.shipping_module_code as shipping_method,
					m.ip_address as remote_ip,
					i.products_id as product_id,
					i.products_name as products_name,
					i.products_price as base_cost,
					i.products_price as price_org,
					i.final_price as final_price,
					i.products_quantity as qty
					from {$this->main_table} m left join {$this->item_table} i on m.orders_id = i.orders_id where m.orders_id={$order_id}";
		//echo $query.'<br /><br />';
		//exit();		
		
		$this->s_db->query( $query );
		return $this->s_db->af();	
	}
	
	private function getOrderComment( $order_id )
	{
		$order_comment = '';
		$query = "select 
				*
				from orders_status_history where orders_id={$order_id} ";
		$this->s_db->query( $query );		
		$comments = $this->s_db->af();
		foreach( $comments as $_comment ){
			$order_comment.= $_comment['carrier_name'].$_comment['comments']."\n";
		}
		
		return $order_comment;
	}
	
	private function getShippmentData( $order_id )
	{
		$query = "select 
				*
				from orders_status_history where orders_id={$order_id} and (carrier_name is not null or carrier_name!='' or orders_status_id =4)";
		$this->s_db->query( $query );
		
		return $this->s_db->af();
	}
	
	private function getLastUpdatedDate( $order_id )
	{
		$query = "select 
				date_added
				from orders_status_history where orders_id={$order_id} order by date_added DESC limit 1";
		$this->s_db->query( $query );
		
		$row = $this->s_db->fetch_array();
		return $row['date_added'];
	}
	
	private function importShippment( $order,$carrier_name,$tracking_number )
	{
		try{
			$order = Mage::getModel("sales/order")->load( $order->getId() );
			
			//check shipment org
			if( $order->getShipmentsCollection() && $order->getShipmentsCollection()->getSize() ){
				foreach( $order->getShipmentsCollection() as $_shipment ){
					
					$shipment = Mage::getModel('sales/order_shipment')->load($_shipment->getData('entity_id'));
					$exist = false;
					foreach( $_shipment->getAllTracks() as $_track ){
						if( $tracking_number == $_track->getData('number') )
							$exist = true;
					}
					
					if ( $exist ) {
						//Mage::log("------{$index}-----Tracking number already exist.#{$order_id}-Tracking No.{$tracking_number}",Zend_Log::INFO,"erp_order_shipment.log");
						return true;
					}else{
						//add track
						$track = Mage::getModel('sales/order_shipment_track')
								->setNumber($tracking_number)
								->setCarrierCode('custom')
								->setTitle($carrier_name);
						$shipment->addTrack($track)
								->save();	
						return true;
					}
				}
			}else{	
					echo "first into tracking.<br>";
					$convertor  = Mage::getModel('sales/convert_order');
					$shipment    = $convertor->toShipment($order);			
					
					//$savedQtys = $this->_getItemQtys();
					foreach ($order->getAllItems() as $orderItem) {
						
						if ($orderItem->getIsVirtual()) {
							continue;
						}
						$item = $convertor->itemToShipmentItem($orderItem);
						$qty = $orderItem->getQtyToShip();			
						$item->setQty($qty);			
						$shipment->addItem($item);
					}
									
					$trackno['carrier_code'] = 'custom';
					$trackno['title'] = $carrier_name;
					$trackno['number'] = $tracking_number;
					
					$track = Mage::getModel('sales/order_shipment_track')
							->addData($trackno);
					$shipment->addTrack($track);
					$shipment->register();
					
					//save shipment
					$shipment->getOrder()->setIsInProcess(true);
					Mage::getModel('core/resource_transaction')
						->addObject($shipment)
						->addObject($shipment->getOrder())
						->save();
									
					return true;
			}
		}catch( Exception $e ){
				return false;
		}
	}
	
	private function formatOrderItem( $item_data )
	{
		$item = Mage::getModel("sales/order_item");
		$product = $this->getProduct( $item_data['product_id'] );
		$product->setData( 'description','' );
		$item_data['row_subtotal'] = $item_data['qty']*$item_data['final_price'];
		$item->setData( $product->getData() );
		$item->setData( 'qty_ordered',$item_data['qty'] );
		$item->setData( 'base_row_total',$item_data['row_subtotal'] );
		$item->setData( 'row_total',$item_data['row_subtotal'] );
		$item->setData( 'base_cost',$item_data['base_cost'] );
		$item->setData( 'original_price',$item_data['price_org'] );
		$item->setData( 'base_original_price',$item_data['price_org'] );
		$item->setData( 'row_weight',$item_data['qty']*$product->getWeight() );
				
		return $item;
	}
	
	private function getProduct( $org_product_id )
	{
		$query = "select * from {$this->log_table} where org_product_id  = '{$org_product_id}'";
		
		$this->s_db->query( $query );
		$log = $this->s_db->fetch_array();
		
		return Mage::getModel("catalog/product")->load( $log['product_id'] );
	}
	
	public function formatOrderData( $order )
	{
		global $config;
		
		$_order = array();
		if( isset( $config['shipping_method_mapping'][$order['shipping_method']] ) )
			$shipping_method_code = $config['shipping_method_mapping'][$order['shipping_method']];
		else $shipping_method_code = $config['default_shipping_method'];
		
		$order_status = '';
		
		if( isset( $config['order_status_mapping'][$order['status']] ) )
			$order_status = $config['order_status_mapping'][$order['status']];
		else $order_status = $config['order_status_mapping'][$order['status']][1];
		
		$totals = $this->getOrderTotals( $order['increase_id'] );
		
		$_order['increment_id'] = $order['increase_id'];
		$_order['store_id'] = $config['default_store'];
		$_order['status'] = $order_status;
		$_order['remote_ip'] = $order['remote_ip'];
		$_order['x_forwarded_for'] = ''; 
		if( $order['customer_id'] ){
			$customer = $this->getCustomer( $order['customer_id'] );
			//print_r( $customer->getData() );
			$_order['customer_id'] = $customer->getId(); 
			$_order['customer_email'] = $customer->getEmail();
			$_order['customer_prefix'] = ''; 
			$_order['customer_firstname'] = $customer->getFirstname(); 
			$_order['customer_middlename'] = $customer->getMiddlename(); 
			$_order['customer_lastname'] = $customer->getLastname(); 
			$_order['customer_suffix'] = $customer->getSuffix(); 
			$_order['customer_group_id'] = $customer->getGroupId(); 
			$_order['customer_tax_class_id'] = $customer->getTaxClassId(); 
			$_order['customer_note'] = $customer->getNote(); 
			$_order['customer_note_notify'] = $customer->getNoteNotify(); 
			$_order['customer_is_guest'] = 0;
			$_order['customer_dob'] = $customer->getDob(); 
			$_order['customer_taxvat'] = $customer->getTaxVat(); 
			$_order['customer_gender'] = $customer->getGender(); 
		}else{
			$_order['customer_email'] = $order['email'];
			$_order['customer_is_guest'] = 1;
		}
		$_order['quote_base_grand_total'] = $order['grand_total']; 
		$_order['global_currency_code'] = 'USD';
		$_order['base_currency_code'] = 'USD';
		$_order['store_currency_code'] = 'USD';
		$_order['order_currency_code'] = 'USD';
		$_order['store_to_base_rate'] = 1.0000;
		$_order['store_to_order_rate'] = 1.0000;
		$_order['base_to_global_rate'] = 1.0000;
		$_order['base_to_order_rate'] = 1.0000;
		$_order['coupon_code'] = ''; 
		$_order['is_virtual'] = 0;
		$_order['is_multi_payment'] = ''; 
		$_order['applied_rule_ids'] = ''; 
		$_order['total_qty_ordered'] =1;
		$_order['gift_message_id'] = ''; 
		$_order['weight'] = 1; 
		$_order['shipping_method'] = $shipping_method_code;
		$_order['shipping_description'] = $shipping_method_code;
		$_order['shipping_rate'] = ''; 
		$_order['subtotal'] = $totals['ot_subtotal']; 
		$_order['tax_amount'] = $totals['ot_tax']; 
		$_order['tax_string'] = '';
		$_order['discount_amount'] = $totals['ot_coupon']; 
		$_order['shipping_amount'] = $totals['ot_shipping']; 
		$_order['shipping_incl_tax'] = $totals['ot_shipping']; 
		$_order['shipping_tax_amount'] = 0;
		$_order['custbalance_amount'] = '';
		$_order['grand_total'] = $order['grand_total']; 
		$_order['base_subtotal'] = $totals['ot_subtotal']; 
		$_order['base_tax_amount'] = $totals['ot_tax']; 
		$_order['base_discount_amount'] = 0;
		$_order['base_shipping_amount'] = $totals['ot_shipping']; 
		$_order['base_shipping_incl_tax'] = $totals['ot_shipping'];
		$_order['base_shipping_tax_amount'] = 0;
		$_order['base_custbalance_amount'] = '';
		$_order['base_grand_total'] = $order['grand_total']; 
		$_order['hidden_tax_amount'] = 0;
		$_order['base_hidden_tax_amount'] = 0;
		$_order['shipping_hidden_tax_amount'] = 0;
		$_order['base_shipping_hidden_tax_amount'] = 0;
		$_order['discount_description'] = '';
		$_order['shipping_discount_amount'] = 0;
		$_order['base_shipping_discount_amount'] = 0;
		$_order['subtotal_incl_tax'] = $totals['ot_subtotal']; 
		$_order['base_subtotal_incl_tax'] = $totals['ot_subtotal']; 
		$_order['created_at'] = $order['created_at']; 
		$_order['updated_at'] = $this->getLastUpdatedDate( $order['increase_id'] ); 
		
		return $_order;
	}
	
	private function getOrderTotals( $order_id )
	{
		$totals = array();
		$query = "select * from orders_total where orders_id  = '{$order_id}'";
		$this->s_db->query( $query );
		$rows = $this->s_db->af();
		foreach( $rows as $_row ){
			$totals[ $_row['class'] ] = $_row['value'];
		}
		return $totals;
	}
	
	private function getCustomer( $org_customer_id )
	{
		$query = "select * from {$this->log_table} where org_customer_id  = '{$org_customer_id}'";
		
		$this->s_db->query( $query );
		$log = $this->s_db->fetch_array();
		
		return Mage::getModel("customer/customer")->load( $log['customer_id'] );
	}
	
	private function formatOrderPayment( $method_code )
	{
		global $config;
		$payment = Mage::getModel("sales/order_payment");
		if( isset( $config['payment_method_mapping'][$method_code] ) )
			$payment_method_code = $config['payment_method_mapping'][$method_code];
		else $payment_method_code = $config['default_payment_method'];
		$payment->setData( 'method',$payment_method_code );
		return $payment;
	}
	
	
	
	private function formatOrderBillingAddress( $data )
	{
		
		$address = Mage::getModel("sales/order_address");
		$address->setData( 'address_type',$address_type );
		$address->setData( 'firstname',substr($data['billing_name'],0,strpos( $data['billing_name'],' ' )-1 ) );
		$address->setData( 'lastname',substr($data['billing_name'],strpos( $data['billing_name'],' ' ) ) );
		$address->setData( 'company',$data['billing_company'] );
		$address->setData( 'street',$data['billing_street_address'] );
		$address->setData( 'city',$data['billing_city'] );
		$address->setData( 'region',$data['billing_state'] );
		$address->setData( 'postcode',$data['billing_postcode'] );
		$address->setData( 'country_id',$this->getCountryCode($data['billing_country']) );
		$address->setData( 'telephone',$data['telephone'] );
		$address->setData( 'fax',$data['fax'] );
		$address->setData( 'address_type','billing' );
	
		return $address;
	}
	
	private function formatOrderShippingAddress( $data )
	{
		$address = Mage::getModel("sales/order_address");
		$address->setData( 'address_type',$address_type );
		$address->setData( 'firstname',substr($data['delivery_name'],0,strpos( $data['delivery_name'],' ' )-1 ) );
		$address->setData( 'lastname',substr($data['delivery_name'],strpos( $data['delivery_name'],' ' ) ) );
		$address->setData( 'company',$data['delivery_company'] );
		$address->setData( 'street',$data['delivery_street_address'] );
		$address->setData( 'city',$data['delivery_city'] );
		$address->setData( 'region',$data['delivery_state'] );
		$address->setData( 'postcode',$data['delivery_postcode'] );
		$address->setData( 'country_id',$this->getCountryCode($data['delivery_country']) );
		$address->setData( 'telephone',$data['telephone'] );
		$address->setData( 'fax',$data['fax'] );
		$address->setData( 'address_type','shipping' );
		return $address;
	}
	
	private function getCountryCode( $name )
	{
		$query = "select * from countries where countries_name  = '{$name}' limit 1";
		
		$this->s_db->query( $query );
		$country = $this->s_db->fetch_array();
		return $country['countries_iso_code_2']?$country['countries_iso_code_2']:'US';
	}

}